package com.city.repository.pub;

import com.city.domain.pub.PubUser;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @deprecation:基本用户信息表
 * @dmail: 398521613@qq.com
 * @author: L-H
 * @date: 2018-07-18 13:14
 */
public interface PubUserRepository extends JpaRepository<PubUser,Integer> {

    @Query(value = "select * from pub_user where tellphone=?1", nativeQuery = true)
    PubUser getUserByTell(String tell);

    @Query(value = "select * from pub_user where id_card=?1", nativeQuery = true)
    PubUser getUserByIdCard(String idCard);

    @Query(value = "select * from pub_user where name=?1 and tellphone=?2", nativeQuery = true)
    PubUser getUserByTellAndName(String name, String tellphone);


    @Query(value = "select * from pub_user where id>=?1 and id<=?2 order by id desc", nativeQuery = true)
    List<PubUser> findAllByIdLimit(Integer left,Integer right);

    /**
     * @Author: 李顺时
     * @Description: 查询Pub_user用户
     * @Date: 16:43 2018/8/12
     */
    @Query(value = "select * from pub_user where family_id=1 order by id desc limit ?1,?2", nativeQuery = true)
    List<PubUser> findAllByIdLimit2(Integer left,Integer right);

    @Query(value = "select count(*) from pub_user where family_id=1 order by id desc", nativeQuery = true)
    long countfindAllByIdLimit2();

    /**
     * @Author: 李顺时
     * @Description: 查询户主家庭人员
     * @param householdId 户主ID
     * @Date: 17:26 2018/8/12
     */
    @Query(value = "select * from pub_user where household_id =?1 and family_id=0 ", nativeQuery = true)
    List<PubUser> findFamilyMenberByHouseholdId(Integer householdId);

//    /**
//     * @Author: 李顺时
//     * @Description: 查询户主家庭人员姓名
//     * @param householdId 户主ID
//     * @Date: 17:26 2018/8/12
//     */
//    @Query(value = "select name from pub_user where household_id =?1 and family_id=0 ", nativeQuery = true)
//    List<String> findFamilyMenberNameByHouseholdId(Integer householdId);

    /**
     * @Author: 李顺时
     * @Description: 查询户主家庭人员姓名
     * @param householdId 户主ID
     * @Date: 17:26 2018/8/12
     */
    @Query(value = "call search_family_menber(?1) ", nativeQuery = true)
    List<String> findFamilyMenberNameByHouseholdId(Integer householdId);


    /**
     * @Author: 李顺时
     * @Description: 通过姓名、电话、身份证号等模糊查询
     * @Date: 20:17 2018/8/12
     */
    @Query(value = "SELECT * FROM pub_user WHERE NAME LIKE ?1 OR id_card like ?1 or tellphone like ?1 limit ?2,?3", nativeQuery = true)
    List<PubUser> seachUser(String keyword,Integer page,Integer num);


    @Query(value =  "SELECT * FROM pub_user " +
                    "WHERE id = ?1",nativeQuery = true)
    PubUser findById(Integer id);

    @Query(value = "select * from pub_user order by id desc limit ?1,?2",nativeQuery = true)
    List<PubUser> findByAuthLimit(Integer page,Integer limit);

    /**
     * @Author: 李顺时
     * @Description: 通过姓名查询所有用户  用于gis以房找人的搜索
     * @Date: 17:04 2018/8/24
     */
    @Query(value = "SELECT * FROM pub_user WHERE NAME = ?1 and household_id != '' limit 100", nativeQuery = true)
    List<PubUser> seachUserByName(String keyword);


    @Query(value =  "SELECT * FROM pub_user " +
                    "WHERE tellphone = ?1",nativeQuery = true)
    PubUser findByLoginTel(String loginTel);



    /**
     * @author  王宇
     * @date 2018/9/2
     * @description  通过身份证号 和id查询用户
     */

    @Query(value = "SELECT * FROM pub_user WHERE id_card=?1 and id=?2",nativeQuery = true)
    PubUser findByIdCardAndId(String idCard,int id);


    /**
     * @author  王宇
     * @date 2018/9/9
     * @description  通过部门id 查询人员列表
     */

    @Query(value = "select p.* from pub_user p ,ct_user_branch c where p.id = c.user_id and c.branch_id=?1 ",nativeQuery = true)
    List<PubUser> findByBranchId(Integer branchId);


    @Query(value = "SELECT count(*) FROM pub_user WHERE NAME LIKE ?1 OR id_card like ?1 or tellphone like ?1", nativeQuery = true)
    long seachUserAll(String keyword);

}
